package itsm.isperp.framework.data.excel.executor;

import itsm.isperp.framework.data.excel.ExcelData;
import itsm.isperp.framework.data.excel.ExcelStruct;
import itsm.isperp.framework.data.excel.ImportCellDesc;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellReference;

/**
 * 根据描述文件，读取Excel的内容
 * 
 */
public class ExcelDataReader {

	/**
	 * 
	 * @param excelStruct
	 * @param importExcelStream
	 * @param sheetName
	 * @return
	 * @throws IOException
	 */
	public static ExcelData readExcel(ExcelStruct excelStruct, Sheet sheet)
			throws IOException {
		if (excelStruct == null || sheet == null) {
			return null;
		}
		return readExcel(excelStruct, sheet.getWorkbook(), sheet);
	}

	/**
	 * 读取并组装Excel中的信息
	 * 
	 * @param excelStruct
	 *            Excel结构描述文件
	 * @param importExcelStream
	 *            被导入的Excel
	 * @param sheetIndex
	 *            读取的sheetIndex，从0开始
	 * @return 组装后的Excel数据
	 */
	public static ExcelData readExcel(ExcelStruct excelStruct,
			InputStream importExcelStream, int sheetIndex) throws IOException {
		if (excelStruct == null || importExcelStream == null) {
			return null;
		}

		// 读取excel
		Workbook wb = new HSSFWorkbook(importExcelStream);
		Sheet sheet = wb.getSheetAt(sheetIndex);
		return readExcel(excelStruct, wb, sheet);
	}

	private static ExcelData readExcel(ExcelStruct excelStruct, Workbook wb,
			Sheet sheet) {

		// 1.判断有无重复导入的数据
		// 2.如果没有，则直接读取一次数据，并返回
		// 3.如果有，则得到循环的记录行数量 repeatCount，和重复开始行repeatBeginRow
		// 4.读取所有的重复数据，读取的过程中，读取到终止时终止
		// 5.根据repeatCount、repeatBeginRow来更新一次性导入数据的行号（从1开始）
		// 6.读取所有的一次性数据
		ExcelData excelData = new ExcelData();
		FormulaEvaluator evaluator = wb.getCreationHelper()
				.createFormulaEvaluator();
		// 1. 2.
		List<ImportCellDesc> repeatImportCells = excelStruct
				.getRepeatImportCells();
		if (repeatImportCells == null || repeatImportCells.size() <= 0
				|| StringUtils.isEmpty(excelStruct.getEndCode())) {
			return excelData;
		}
		// 3.
		repeatImportCells.get(0).getCellRow();
		// 4.
		// 重复导入的次数
		readRepeatImportData(repeatImportCells, sheet, evaluator,
				excelStruct.getEndCode(), excelData);

		return excelData;
	}

	/**
	 * 读取重复数据
	 * 
	 * @param repeatImportCells
	 *            重复数据的描述
	 * @param excelData
	 *            需要组装的数据
	 */
	private static int readRepeatImportData(
			List<ImportCellDesc> repeatImportCells, Sheet sheet,
			FormulaEvaluator evaluator, String endCode, ExcelData excelData) {
		int repeatCount = 0;
		boolean endFlag = false; // 是否到达终止标识
		if (repeatImportCells == null || repeatImportCells.size() <= 0
				|| StringUtils.isEmpty(endCode) || sheet == null
				|| sheet.getLastRowNum() <= 0) {
			return repeatCount;
		}
		List<Map<String, ImportCellDesc>> repeatData = new ArrayList<Map<String, ImportCellDesc>>();
		int beginRow = repeatImportCells.get(0).getCellRow() - 1;
		excelData.setRepeatData(repeatData);
		for (int i = beginRow; i <= sheet.getLastRowNum(); i++) {
			Map<String, ImportCellDesc> map = new HashMap<String, ImportCellDesc>();
			Row row = sheet.getRow(i);
			if (row == null) {
				continue;
			}
			for (ImportCellDesc desc : repeatImportCells) {
				// 一定要使用新的desc，否则会有问题。
				ImportCellDesc newDesc = new ImportCellDesc(desc);

				CellReference ref = new CellReference(i,
						newDesc.getCellCol() - 1);
				newDesc.setCellRef(ref.formatAsString()); // 重新设置位置

				map.put(newDesc.getFieldName(), newDesc);
				Cell cell = row.getCell(newDesc.getCellCol() - 1);
				if (cell == null) {
					continue;
				}
				// 获取值
				Object fieldValue = getCellValue(evaluator, cell);
				if (endCode.equals(fieldValue)) {// 终止了
					endFlag = true;
					break;
				}
				newDesc.setFieldValue(fieldValue);
				ValidateUtil.validateCell(excelData, newDesc);
			}
			if (endFlag) {// 表示已经读到最后一行了
				break;
			}
			repeatCount++; // 成功添加一行记录
			repeatData.add(map);
		}
		if (!endFlag) {
			// System.out.println("excel中未找到结束字符 : " + endCode);
		}

		return repeatCount;
	}

	/**
	 * 获取单元格的值
	 */
	private static Object getCellValue(FormulaEvaluator evaluator, Cell cell) {
		Object fieldValue = "";
		if (cell != null) {
			switch (evaluator.evaluateInCell(cell).getCellType()) {
			case Cell.CELL_TYPE_STRING: // 字符串
				fieldValue = cell.getStringCellValue();
				break;
			case Cell.CELL_TYPE_BOOLEAN: // bool型
				fieldValue = cell.getBooleanCellValue() + "";
				break;
			case Cell.CELL_TYPE_NUMERIC: // 数值型
				// 判断单元格是否属于日期格式
				if (DateUtil.isCellDateFormatted(cell)) {
					SimpleDateFormat formatter;
					formatter = new SimpleDateFormat("yyyy-MM-dd");
					fieldValue = formatter
							.format(HSSFDateUtil.getJavaDate(cell
									.getNumericCellValue())).toString();
				} else {
					cell.setCellType(1);
					fieldValue = cell.getStringCellValue();
				}
				break;
			case Cell.CELL_TYPE_BLANK: // 空
				fieldValue = "";
				break;
			case Cell.CELL_TYPE_ERROR:
				fieldValue = cell.getErrorCellValue() + "";
				break;
			case Cell.CELL_TYPE_FORMULA:
				fieldValue = "";
				break;
			}
		}
		return fieldValue;
	}

}